package thesis.database;

import java.util.Date;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;

public class RouteJDBCTemplate implements RouteDAO {
	private DataSource dataSource;
	private JdbcTemplate jdbcTemplateObject;

	@Override
	public void setDataSource(DataSource ds) {
		this.dataSource = ds;
		this.jdbcTemplateObject = new JdbcTemplate(dataSource);

	}

	@Override
	public void create(Route route) {
		String sql = "insert into route(taxi_id,status,latitude,longitude,speed,time,gps_data)"
				+ "values ( ?, ?, ?, ?, ?, ?, ?)";
		jdbcTemplateObject.update(sql, route.getTaxiId(), route.getStatus(),
				route.getLatitude(), route.getLongitude(), route.getSpeed(),
				route.getTime(), route.getGpsData());
		System.out.println("Created Route Record");

	}

	@Override
	public Route getRoute(Integer id) {
		String sql = "select * from route where id = ?";
		Route route = jdbcTemplateObject.queryForObject(sql,
				new Object[] { 1 }, new RouteMapper());
		return route;
	}

	@Override
	public List<Route> listRoutes() {
		String sql = "select * from route";
		List<Route> list = jdbcTemplateObject.query(sql, new RouteMapper());
		return list;
	}

	@Override
	public List<Route> getRoutesIn10Days(Integer id) {
		String sql = "select * from route where taxi_id = ? and time between SYSDATE() - INTERVAL 10 DAY AND SYSDATE()";
		List<Route> list = jdbcTemplateObject.query(sql, new Object[] { id },
				new RouteMapper());

		return list;
	}

	@Override
	public void delete(Integer id) {
		String sql = "delete from route where id = ?";
		jdbcTemplateObject.update(sql, id);
		System.out.println("Deleted Route Record with ID = " + id);

	}

	@Override
	public void update(Route route) {
		String sql = "update route set taxi_id = ? ,status = ? ,latitude = ? ,longitude = ?,speed = ? ,time = ?,gps_data = ? where id = ?";
		jdbcTemplateObject.update(sql, route.getTaxiId(), route.getStatus(),
				route.getLatitude(), route.getLongitude(), route.getSpeed(),
				route.getTime(), route.getGpsData(), route.getId());
		System.out.println("Updated Route Record with ID = " + route.getId());
	}

	@Override
	public List<Route> getRouteById(int id, String start, String end) {
		String sql = "select * from route where taxi_id = ? and time >= ? and time <= ?";
		List<Route> list = jdbcTemplateObject.query(sql, new Object[] { id,
				start, end }, new RouteMapper());
		return list;
	}

}
